import json
import os

import openpyxl
from openpyxl.worksheet.worksheet import Worksheet

from common.config.config import file_path


class DoExcel:
	#读全部
	def readExcelS(self, f_path):
		"""
		efile = os.path.join(fail_path,fname)
		先把要读取的文件路径交给 oppenpyxl 的载入函数里边
		将其加载给工作薄 workbook
		并将其激活 赋给表 sheet
		"""
		#载入一个工作簿
		workbook = openpyxl.load_workbook(f_path)
		# 激活
		sheet1 = workbook.active

		# 将元素传给列表 并返回
		l = [i for i in sheet1.values]
		return l

	#读部分
	def readExcelRowCol(self, f_path,sheet_name,minR=None, maxR=None, minC=None, maxC=None):
		workbook = openpyxl.load_workbook(f_path)

		# sheet1 = workbook.active

		sheet1:Worksheet=workbook.get_sheet_by_name(sheet_name)
		if maxR is None:
			maxR = sheet1.max_row
		# #读sheet中的内容
		a = sheet1.iter_rows(min_row=minR, max_row=maxR, min_col=minC, max_col=maxC, values_only=True)

		l = [i for i in a]
		# print(l)
		return l

	#写文件
	def writeExcel(self, f_path, sheet_name, start_row=None, start_col=None, values=None):
		#创建文件
		wk = openpyxl.Workbook()
		#获取工作表默认第一张表
		st = wk.active
		#重命名表
		st.title = sheet_name
		if start_row is not None and start_col is not None and values is not None:
			st.cell(row=start_row, column=start_col,value=values)

		wk.save(f_path)

	def changeJson(self,f_path,d):
		with open(f_path,mode='w+',encoding='utf-8') as f:
			json.dump(d,f,sort_keys=True,ensure_ascii=False)


	def changeDir(self,f_path):
		with open(f_path,mode='r+',encoding='utf-8') as f:
			d=json.load(f)
			return d

if __name__ == '__main__':
	do = DoExcel()

	file_path1 = os.path.join(file_path, 'test1.xlsx')
	she_name = "Sheet1"

	# do.writeExcel(file_path, she_name, 1, 2, 'nihao')
	a=do.readExcelS(file_path1)
	# print(a)
	do.readExcelRowCol(file_path1, minR=1, maxR=2, minC=1, maxC=3)
	file_path = os.path.join(file_path, 'test.json')
	d = {'name': 'zhangsan', 'age': 18, 'sex': '男', 'class': 'one'}
	# do.changeJson(file_path,d)
	print(do.changeDir(file_path))